package databases

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/spf13/viper"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
)

var handler *sql.DB
var handler2 *gorm.DB

func GetMysqlDB() *sql.DB {
	if handler != nil {
		return handler
	}
	db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local", viper.GetString("mysql.username"), viper.GetString("mysql.password"), viper.GetString("mysql.host"), viper.GetInt("mysql.port"), viper.GetString("mysql.dbname")))
	if err != nil {
		return nil
	}

	// 测试数据库连接
	err = db.Ping()
	if err != nil {
		fmt.Println("Error pinging the database:", err)
		return nil
	}
	handler = db
	return db
}

func GetGorm() *gorm.DB {
	if handler2 != nil {
		return handler2
	}
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local", viper.GetString("mysql.username"), viper.GetString("mysql.password"), viper.GetString("mysql.host"), viper.GetInt("mysql.port"), viper.GetString("mysql.dbname"))
	open, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			TablePrefix:   viper.GetString("mysql.prefix"), // 为所有表添加前缀
			SingularTable: true,                            // 禁用复数表名
		},
	})
	if err != nil {
		log.Fatalf("failed to connect to database: %v", err)
	}
	sqlDB, err := open.DB()
	if err == nil {
		SetConnMaxLifetime(sqlDB)
	}
	handler2 = open
	return open
}

// SetConnMaxLifetime
// [mysql] 2025/03/11 17:36:01 packets.go:149 write tcp 127.0.0.1:50154->127.0.0.1:3306: wsasend: An established connection was aborted by the software in your host machine.
// [mysql] connection.go:173: bad connection 和 invalid connection
// maxLifeTime必须要比mysql服务器设置的wait_timeout小，否则会导致golang侧连接池依然保留已被mysql服务器关闭了的连接。
// SHOW VARIABLES LIKE 'wait_timeout'; 查看wait_timeout
// 一般建议wait_timeout/2
func SetConnMaxLifetime(db *sql.DB) bool {
	variable := struct {
		variableName string
		waitTimeout  int
	}{}
	err := db.QueryRow(fmt.Sprintf("SHOW VARIABLES LIKE '%s'", "wait_timeout")).Scan(&variable.variableName, &variable.waitTimeout)
	if err == nil {
		db.SetConnMaxLifetime(time.Second * time.Duration(variable.waitTimeout/2))
		return true
	}
	return false
}

func DeferClose() {
	if handler != nil {
		handler.Close()
	}
	if handler2 != nil {
		sqlDB, _ := handler2.DB()
		sqlDB.Close()
	}
}
